rm(list=ls())
library(tidyverse)

# Function to create datase with observations per muncode-year and time-variable columns 
center <-function(df,year_center){
  df <- df %>% 
    mutate(center = year - year_center) %>% 
    select(-year) %>%
    filter(between(center,-3,4)) %>% 
    mutate(center = str_replace(ifelse(center<0,paste0("t_minus",center),paste0("t_plus",center)),"-",""),
           center = str_replace(center,"t_plus0","t_0")) %>% 
    gather(variable, value, -c(mun_code,center)) %>%  
    unite(center,variable,center) %>%
    spread(center,value) %>% 
    mutate(year = year_center) 
  return(df)
}

# Create function to read data and match both datasets
read_amendments <- function(year){
  # read data
  parecer <- read_delim(here::here("data","raw","amendments",paste0("amendments_",year,".csv")),
                        delim = ",",
                        locale = locale(encoding = "UTF-8", decimal_mark = ","),
                      col_types = str_c(c(rep("c",11),"d"), collapse = "")) 
  
  execucao <- read_delim(here::here("data","raw","amendments",paste0("expenditure_execution_",year,".csv")),
                         delim = ",",
                         locale = locale(encoding = "UTF-8", decimal_mark = ","),
                         col_types = str_c(c(rep("c",7),rep("d",6)), collapse = ""))
  
  # Prepare data for join
  parecer <- parecer %>% 
    mutate(cod = paste0(UF,Funcional,`UO (Cod)`, `Localidade (Cod)`, sep=""))
  
  execucao <- execucao %>% 
    mutate(cod = paste0(UF,Funcional,`UO (Cod)`, `Localidade (Cod)`, sep="")) %>% 
    group_by(UF,Funcional,cod) %>% 
    summarise(across(`Dotação Inicial`:Pago,sum, na.rm = T)) %>% 
    ungroup() %>% 
    select(cod,`Dotação Inicial`:Pago)
  
  # Join and create year variable
  data <- parecer %>% 
    left_join(execucao, by = c("cod")) %>% 
    mutate(Ano = year) 
  
  return(data)
}

amendments_data <- map_df(2003:2014,read_amendments)

# Filter only amendments directed at cities (not states or regions)
amendments_data <- amendments_data %>% 
  mutate(`Localidade (Tipo)` = str_to_upper(stringi::stri_trans_general(`Localidade (Tipo)`, "latin-ascii")))

amendments_data %>% distinct(`Localidade (Tipo)`)

amendments_mun <- amendments_data %>% 
  filter(`Localidade (Tipo)` == "MUNICIPIO")

# Aggregate at the city level
amendments_mun <- amendments_mun %>% 
  group_by(`Localidade (Cod)`,Localidade,Ano) %>% 
  summarise(across(`Dotação Inicial`:Pago,sum, na.rm = T)) %>% 
  ungroup()

# Select variables and create mun_code
amendments_mun <- amendments_mun %>% 
  mutate(mun_code = str_sub(`Localidade (Cod)`,1,-2)) %>% 
  select(mun_code,
         mun_name = Localidade,
         year = Ano,
         amendment_authorized = Autorizado,
         amendment_executed = `Despesa Executada (Favorecido)`) 

# Deflate data
gdp_deflator <- read_rds(here::here("data","processed","gdp_deflator.rds"))

amendments_mun <- amendments_mun %>% 
  left_join(gdp_deflator, by = c("year")) 

# Deflate and measure in thousands R$
amendments_mun <- amendments_mun %>% 
  mutate(across(starts_with("amendment"),~./1000)) %>% 
  mutate(across(starts_with("amendment"),~./gdp_deflator,.names = "{.col}_cp")) %>% 
  select(-gdp_deflator,-mun_name)

# Prepare final measures (year of election and sum of 2 years)
amendments_mun_outcomes <- map(seq(2004,2012, by = 4), center, df = amendments_mun) %>% 
  bind_rows() %>% 
  select(mun_code,year, everything()) %>%  
  mutate_at(vars(matches("_t_")),
            .funs = funs(as.numeric(.)))

# Keep only measure we use and election year and the year before and change NA to zero
amendments_mun_outcomes <- amendments_mun_outcomes %>% 
  select_at(vars(mun_code, year, starts_with("amendment"))) %>% 
  select_at(vars(mun_code, year, ends_with("t_minus1"),ends_with("t_0"))) %>% 
  mutate(across(starts_with("amendment"),~ifelse(is.na(.),0,.))) 
  
# SaveRdS ----

write_rds(amendments_mun_outcomes,here::here("data","processed","citycharacteristics","amendments_outcomes.rds"))
